package com.ops.uitl.jdbc;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Timestamp;

import org.apache.log4j.Logger;
import com.ops.uitl.common.Common;
import com.ops.uitl.data.DataMap;
import com.ops.uitl.data.DatasetList;
import com.ops.uitl.data.DatasetResult;
import com.ops.uitl.data.IData;
import com.ops.uitl.data.IDataset;
import com.ops.uitl.file.FileMan;

public class DaoManager {

	protected Logger log = Logger.getLogger(this.getClass());
	protected Common common = Common.getInstance();

	private Connection[] conns;

	/**
	 * construct function
	 * @param conn
	 * @throws Exception
	 */
	public DaoManager(Connection conn) throws Exception {
		this.conns = new Connection[] { conn };
	}

	/**
	 * construct function
	 * @param conns
	 * @throws Exception
	 */
	public DaoManager(Connection[] conns) throws Exception {
		this.conns = conns;
	}

	/**
	 * execute query
	 * @param conn
	 * @param sql
	 * @return ResultSet
	 * @throws Exception
	 */
	private ResultSet executeQuery(Connection conn, String sql) throws Exception {
		IStatement statement = new SimpleStatement(conn, sql);
		return statement.executeQuery();
	}

	/**
	 * execute query
	 * @param sql
	 * @return ResultSet
	 * @throws Exception
	 */
	public ResultSet executeQuery(String sql) throws Exception {
		return executeQuery(conns[0], sql);
	}

	/**
	 * execute querys
	 * @param sql
	 * @return ResultSet[]
	 * @throws Exception
	 */
	public ResultSet[] executeQuerys(String sql) throws Exception {
		ResultSet[] rss = new ResultSet[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeQuery(conns[i], sql);
		}
		return rss;
	}

	/**
	 * execute query
	 * @param conn
	 * @param sql
	 * @param param
	 * @return ResultSet
	 * @throws Exception
	 */
	private ResultSet executeQuery(Connection conn, String sql, Parameter param) throws Exception {
		IStatement statement = new ParameterStatement(conn, sql, param);
		return statement.executeQuery();
	}

	/**
	 * execute query
	 * @param sql
	 * @param param
	 * @return ResultSet
	 * @throws Exception
	 */
	public ResultSet executeQuery(String sql, Parameter param) throws Exception {
		return executeQuery(conns[0], sql, param);
	}

	/**
	 * execute querys
	 * @param sql
	 * @param param
	 * @return ResultSet[]
	 * @throws Exception
	 */
	public ResultSet[] executeQuerys(String sql, Parameter param) throws Exception {
		ResultSet[] rss = new ResultSet[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeQuery(conns[i], sql, param);
		}
		return rss;
	}

	/**
	 * execute query
	 * @param conn
	 * @param sql
	 * @param param
	 * @return ResultSet
	 * @throws Exception
	 */
	private ResultSet executeQuery(Connection conn, String sql, IData param) throws Exception {
		IStatement statement = new StdParameterStatement(conn, sql, param);
		return statement.executeQuery();
	}

	/**
	 * execute query
	 * @param sql
	 * @param param
	 * @return ResultSet
	 * @throws Exception
	 */
	public ResultSet executeQuery(String sql, IData param) throws Exception {
		return executeQuery(conns[0], sql, param);
	}

	/**
	 * execute querys
	 * @param sql
	 * @param param
	 * @return ResultSet[]
	 * @throws Exception
	 */
	public ResultSet[] executeQuerys(String sql, IData param) throws Exception {
		ResultSet[] rss = new ResultSet[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeQuery(conns[i], sql, param);
		}
		return rss;
	}

	/**
	 * execute update
	 * @param conn
	 * @param sql
	 * @return int
	 * @throws Exception
	 */
	private int executeUpdate(Connection conn, String sql) throws Exception {
		IStatement statement = new SimpleStatement(conn, sql);
		int result = statement.executeUpdate();
		statement.close();
		return result;
	}

	/**
	 * execute update
	 * @param sql
	 * @return int
	 * @throws Exception
	 */
	public int executeUpdate(String sql) throws Exception {
		return executeUpdate(conns[0], sql);
	}

	/**
	 * execute updates
	 * @param sql
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeUpdates(String sql) throws Exception {
		int[] rss = new int[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeUpdate(conns[i], sql);
		}
		return rss;
	}

	/**
	 * execute update
	 * @parma conn
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	private int executeUpdate(Connection conn, String sql, Object[] param) throws Exception {
		return executeUpdate(conn, sql, new Parameter(param));
	}

	/**
	 * execute update
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	public int executeUpdate(String sql, Object[] param) throws Exception {
		return executeUpdate(conns[0], sql, param);
	}

	/**
	 * execute updates
	 * @param sql
	 * @param param
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeUpdates(String sql, Object[] param) throws Exception {
		int[] rss = new int[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeUpdate(conns[i], sql, param);
		}
		return rss;
	}

	/**
	 * execute update
	 * @param conn
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	private int executeUpdate(Connection conn, String sql, Parameter param) throws Exception {
		IStatement statement = new ParameterStatement(conn, sql, param);
		int result = statement.executeUpdate();
		statement.close();
		return result;
	}

	/**
	 * execute update
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	public int executeUpdate(String sql, Parameter param) throws Exception {
		return executeUpdate(conns[0], sql, param);
	}

	/**
	 * execute updates
	 * @param sql
	 * @param param
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeUpdates(String sql, Parameter param) throws Exception {
		int[] rss = new int[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeUpdate(conns[i], sql, param);
		}
		return rss;
	}

	/**
	 * execute update
	 * @param conn
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	private int executeUpdate(Connection conn, String sql, IData param) throws Exception {
		IStatement statement = new StdParameterStatement(conn, sql, param);
		int result = statement.executeUpdate();
		statement.close();
		return result;
	}

	/**
	 * execute update
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	public int executeUpdate(String sql, IData param) throws Exception {
		return executeUpdate(conns[0], sql, param);
	}

	/**
	 * execute updates
	 * @param sql
	 * @param param
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeUpdates(String sql, IData param) throws Exception {
		int[] rss = new int[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeUpdate(conns[i], sql, param);
		}
		return rss;
	}

	/**
	 * execute batch
	 * @param conn
	 * @param sqls
	 * @return int[]
	 * @throws Exception
	 */
	private int[] executeBatch(Connection conn, String[] sqls) throws Exception {
		IStatement statement = new SimpleStatement(conn, sqls);
		int[] result = statement.executeBatch();
		statement.close();
		return result;
	}

	/**
	 * execute batch
	 * @param sqls
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeBatch(String[] sqls) throws Exception {
		return executeBatch(conns[0], sqls);
	}

	/**
	 * execute batchs
	 * @param sqls
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] executeBatchs(String[] sqls) throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeBatch(conns[i], sqls);
		}
		return rss;
	}

	/**
	 * execute batch
	 * @param conn
	 * @param sqls
	 * @param params
	 * @return int[]
	 * @throws Exception
	 */
	private int[] executeBatch(Connection conn, String sql, Parameter[] params) throws Exception {
		IStatement statement = new ParameterStatement(conn, sql, params);
		int[] result = statement.executeBatch();
		statement.close();
		return result;
	}

	/**
	 * execute batch
	 * @param sql
	 * @param params
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeBatch(String sql, Parameter[] params) throws Exception {
		return executeBatch(conns[0], sql, params);
	}

	/**
	 * execute batchs
	 * @param sql
	 * @param params
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] executeBatchs(String sql, Parameter[] params) throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeBatch(conns[i], sql, params);
		}
		return rss;
	}

	/**
	 * execute batch
	 * @param sql
	 * @param params
	 * @param batchCount
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeBatch(String sql, IDataset params, int batchCount) throws Exception {
		return executeBatch(conns[0], sql, params, batchCount);
	}


	/**
	 * execute batch
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private int[] executeBatch(Connection conn, String sql, IDataset params) throws Exception {
		return executeBatch(conn, sql, params, 2000);
	}

	/**
	 * execute batch
	 * @param conn
	 * @param sqls
	 * @param params
	 * @return int[]
	 * @throws Exception
	 */
	private int[] executeBatch(Connection conn, String sql, IDataset params, int batchCount) throws Exception {
		int size = params.size();
		int index = 0;
		int[] result = new int[size];

		log.debug("batch data : " + params);

		if (batchCount <= 0 ) {
			log.debug("bad batch count, changed to 2000");
			batchCount = 2000;
		}

		int groupCount = size / batchCount + (size % batchCount > 0 ? 1 : 0);

		while(index <= groupCount && index * batchCount < size) {
			int start = index * batchCount;
			int end = (index + 1) * batchCount;

			start = start == 0 ? 0 : index == 0 ? start - 1 : start;
			end = end >= size ? size : end;

			IDataset param = new DatasetList(params.subList(start, end));
			IStatement statement = new StdParameterStatement(conn, sql, param);
			int[] res = statement.executeBatch();
			statement.close();

			for (int i=0; i<res.length; i++) {
				result[start+i] = res[i];
			}
			index ++;
			log.debug("execute batch group from " + (start + 1) + " end " + end);
		}

		return result;
	}

	/**
	 * execute batch
	 * @param sql
	 * @param params
	 * @return int[]
	 * @throws Exception
	 */
	public int[] executeBatch(String sql, IDataset params) throws Exception {
		return executeBatch(conns[0], sql, params);
	}

	/**
	 * execute batchs
	 * @param sql
	 * @param params
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] executeBatchs(String sql, IDataset params) throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = executeBatch(conns[i], sql, params);
		}
		return rss;
	}

	/**
	 * get sys date
	 * @param conn
	 * @return String
	 * @throws Exception
	 */
	private String getSysDate(Connection conn) throws Exception {
		Timestamp time = getCurrentTime(conn);
		return time == null ? null : common.decodeTimestamp("yyyy-MM-dd", time);
	}

	/**
	 * get sys date
	 * @return String
	 * @throws Exception
	 */
	public String getSysDate() throws Exception {
		return getSysDate(conns[0]);
	}

	/**
	 * get sys dates
	 * @return String[]
	 * @throws Exception
	 */
	public String[] getSysDates() throws Exception {
		String[] rss = new String[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getSysDate(conns[i]);
		}
		return rss;
	}

	/**
	 * get sys time
	 * @param conn
	 * @return String
	 * @throws Exception
	 */
	private String getSysTime(Connection conn) throws Exception {
		Timestamp time = getCurrentTime(conn);
		return time == null ? null : common.decodeTimestamp("yyyy-MM-dd HH:mm:ss", time);
	}

	/**
	 * get sys time
	 * @return String
	 * @throws Exception
	 */
	public String getSysTime() throws Exception {
		return getSysTime(conns[0]);
	}

	/**
	 * get sys times
	 * @return String[]
	 * @throws Exception
	 */
	public String[] getSysTimes() throws Exception {
		String[] rss = new String[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getSysTime(conns[i]);
		}
		return rss;
	}

	/**
	 * get current time
	 * @param conn
	 * @return Timestamp
	 * @throws Exception
	 */
	private Timestamp getCurrentTime(Connection conn) throws Exception {
		ResultSet rs = executeQuery(conn, DaoHelper.getCurrentTimeSql());
		Timestamp time = rs.next() ? rs.getTimestamp(1) : null;
		rs.getStatement().close();
		return time;
	}

	/**
	 * get current time
	 * @return Timestamp
	 * @throws Exception
	 */
	public Timestamp getCurrentTime() throws Exception {
		return getCurrentTime(conns[0]);
	}

	/**
	 * get current times
	 * @return Timestamp[]
	 * @throws Exception
	 */
	public Timestamp[] getCurrentTimes() throws Exception {
		Timestamp[] rss = new Timestamp[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getCurrentTime(conns[i]);
		}
		return rss;
	}

	/**
	 * get sequence value
	 * @param conn
	 * @param eparchy_code
	 * @param sequence
	 * @return String
	 * @throws Exception
	 */
	private String getSequence(Connection conn, String eparchy_code, String sequence) throws Exception {
		ResultSet rs = executeQuery(conn, eparchy_code == null ? DaoHelper.getSequenceSql(sequence) : DaoHelper.getSequenceSql(eparchy_code, sequence));
		String value = rs.next() ? rs.getString(1) : null;
		rs.getStatement().close();
		return value;
	}

	/**
	 * get sequence value
	 * @param sequence
	 * @return String
	 * @throws Exception
	 */
	public String getSequence(String sequence) throws Exception {
		return getSequence(conns[0], null, sequence);
	}

	/**
	 * get sequence values
	 * @param sequence
	 * @return String[]
	 * @throws Exception
	 */
	public String[] getSequences(String sequence) throws Exception {
		String[] rss = new String[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getSequence(conns[0], null, sequence);
		}
		return rss;
	}

	/**
	 * get sequence value
	 * @param eparchy_code
	 * @param sequence
	 * @return String
	 * @throws Exception
	 */
	public String getSequence(String eparchy_code, String sequence) throws Exception {
		return getSequence(conns[0], eparchy_code, sequence);
	}

	/**
	 * get sequence values
	 * @param eparchy_code
	 * @param sequence
	 * @return String[]
	 * @throws Exception
	 */
	public String[] getSequences(String eparchy_code, String sequence) throws Exception {
		String[] rss = new String[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getSequence(conns[0], eparchy_code, sequence);
		}
		return rss;
	}

	/**
	 * get count
	 * @param conn
	 * @param sql
	 * @return int
	 * @throws Exception
	 */
	private int getCount(Connection conn, String sql) throws Exception {
		ResultSet rs = executeQuery(conn, DaoHelper.getCountSql(sql));
		int value = rs.next() ? rs.getInt(1) : 0;
		rs.getStatement().close();
		log.debug("getCount()="+value);
		return value;
	}

	/**
	 * get count
	 * @param sql
	 * @return int
	 * @throws Exception
	 */
	public int getCount(String sql) throws Exception {
		return getCount(conns[0], sql);
	}

	/**
	 * get counts
	 * @param sql
	 * @return int[]
	 * @throws Exception
	 */
	public int[] getCounts(String sql) throws Exception {
		int[] rss = new int[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getCount(sql);
		}
		return rss;
	}

	/**
	 * get count
	 * @param conn
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	private int getCount(Connection conn, String sql, Parameter param) throws Exception {
		if (param == null || param.size() == 0) return getCount(conn, sql);
		ResultSet rs = executeQuery(conn, DaoHelper.getCountSql(sql), param);
		int value = rs.next() ? rs.getInt(1) : 0;
		rs.getStatement().close();
		log.debug("getCount()="+value);
		return value;
	}

	/**
	 * get count
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	public int getCount(String sql, Parameter param) throws Exception {
		return getCount(conns[0], sql, param);
	}

	/**
	 * get counts
	 * @param sql
	 * @param param
	 * @return int[]
	 * @throws Exception
	 */
	public int[] getCounts(String sql, Parameter param) throws Exception {
		int[] rss = new int[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getCount(sql, param);
		}
		return rss;
	}

	/**
	 * get count
	 * @param conn
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	private int getCount(Connection conn, String sql, IData param) throws Exception {
		if (param == null) return getCount(conn, sql);
		ResultSet rs = executeQuery(conn, DaoHelper.getCountSql(sql), param);
		int value = rs.next() ? rs.getInt(1) : 0;
		rs.getStatement().close();
		log.debug("getCount()="+value);
		return value;
	}

	/**
	 * get count
	 * @param sql
	 * @param param
	 * @return int
	 * @throws Exception
	 */
	public int getCount(String sql, IData param) throws Exception {
		return getCount(conns[0], sql, param);
	}

	/**
	 * get counts
	 * @param sql
	 * @param param
	 * @return int[]
	 * @throws Exception
	 */
	public int[] getCounts(String sql, IData param) throws Exception {
		int[] rss = new int[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = getCount(sql, param);
		}
		return rss;
	}

	/**
	 * insert data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	private boolean insert(Connection conn, String table_name, IData data) throws Exception {
		Object[] insobjs = DaoHelper.getObjectsByInsert(conn, table_name, data);
		int result = executeUpdate(conn, (String) insobjs[0], (Parameter) insobjs[1]);
		return result == 0 ? false : true;
	}

	/**
	 * insert data
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	public boolean insert(String table_name, IData data) throws Exception {
		return insert(conns[0], table_name, data);
	}

	/**
	 * insert datas
	 * @param table_name
	 * @param data
	 * @return boolean[]
	 * @throws Exception
	 */
	public boolean[] inserts(String table_name, IData data) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = insert(conns[i], table_name, data);
		}
		return rss;
	}

	/**
	 * insert data
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @return int[]
	 * @throws Exception
	 */
	private int[] insert(Connection conn, String table_name, IDataset dataset) throws Exception {
		if (dataset.size() == 0) return null;
		Object[] insobjs = DaoHelper.getObjectsByInsert(conn, table_name, dataset);
		return executeBatch(conn, (String) insobjs[0], (Parameter[]) insobjs[1]);
	}

	/**
	 * insert data
	 * @param table_name
	 * @param dataset
	 * @return int[]
	 * @throws Exception
	 */
	public int[] insert(String table_name, IDataset dataset) throws Exception {
		return insert(conns[0], table_name, dataset);
	}

	/**
	 * insert datas
	 * @param table_name
	 * @param dataset
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] inserts(String table_name, IDataset dataset) throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = insert(conns[i], table_name, dataset);
		}
		return rss;
	}

	/**
	 * update data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	private boolean update(Connection conn, String table_name, IData data) throws Exception {
		return update(conn, table_name, data, null);
	}

	/**
	 * update data
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	public boolean update(String table_name, IData data) throws Exception {
		return update(conns[0], table_name, data);
	}

	/**
	 * update datas
	 * @param table_name
	 * @param data
	 * @return booleans
	 * @throws Exception
	 */
	public boolean[] updates(String table_name, IData data) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = update(conns[i], table_name, data);
		}
		return rss;
	}

	/**
	 * update data
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @return int[]
	 * @throws Exception
	 */
	private int[] update(Connection conn, String table_name, IDataset dataset) throws Exception {
		return update(conn, table_name, dataset, null);
	}

	/**
	 * update data
	 * @param table_name
	 * @param dataset
	 * @return int[]
	 * @throws Exception
	 */
	public int[] update(String table_name, IDataset dataset) throws Exception {
		return update(conns[0], table_name, dataset);
	}

	/**
	 * update datas
	 * @param table_name
	 * @param dataset
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] updates(String table_name, IDataset dataset)  throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = update(conns[i], table_name, dataset);
		}
		return rss;
	}

	/**
	 * update data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean
	 * @throws Exception
	 */
	private boolean update(Connection conn, String table_name, IData data, String[] keys) throws Exception {
		return update(conn, table_name, data, keys, null);
	}

	/**
	 * update data
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean
	 * @throws Exception
	 */
	public boolean update(String table_name, IData data, String[] keys) throws Exception {
		return update(conns[0], table_name, data, keys);
	}

	/**
	 * update datas
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean[]
	 * @throws Exception
	 */
	public boolean[] updates(String table_name, IData data, String[] keys) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = update(conns[i], table_name, data, keys);
		}
		return rss;
	}

	/**
	 * update data
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return int[]
	 * @throws Exception
	 */
	private int[] update(Connection conn, String table_name, IDataset dataset, String[] keys) throws Exception {
		if (dataset.size() == 0) return null;
		Object[] updobjs = DaoHelper.getObjectsByUpdate(conn, table_name, dataset, keys);
		return executeBatch(conn, (String) updobjs[0], (Parameter[]) updobjs[1]);
	}

	/**
	 * update data
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return int[]
	 * @throws Exception
	 */
	public int[] update(String table_name, IDataset dataset, String[] keys) throws Exception {
		return update(conns[0], table_name, dataset, keys);
	}

	/**
	 * update datas
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] updates(String table_name, IDataset dataset, String[] keys)  throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = update(conns[i], table_name, dataset, keys);
		}
		return rss;
	}

	/**
	 * update data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	private boolean update(Connection conn, String table_name, IData data, String[] keys, String[] values) throws Exception {
		Object[] updobjs = DaoHelper.getObjectsByUpdate(conn, table_name, data, keys, values);
		int result = executeUpdate(conn, (String) updobjs[0], (Parameter) updobjs[1]);
		return result == 0 ? false : true;
	}

	/**
	 * update data
	 * @param table_name
	 * @param data
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	public boolean update(String table_name, IData data, String[] keys, String[] values) throws Exception {
		return update(conns[0], table_name, data, keys, values);
	}

	/**
	 * update datas
	 * @param table_name
	 * @param data
	 * @param keys
	 * @param values
	 * @return boolean[]
	 * @throws Exception
	 */
	public boolean[] updates(String table_name, IData data, String[] keys, String[] values) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = update(conns[i], table_name, data, keys, values);
		}
		return rss;
	}

	/**
	 * delete data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	private boolean delete(Connection conn, String table_name, IData data) throws Exception {
		return delete(conn, table_name, data, null);
	}

	/**
	 * delete data
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	public boolean delete(String table_name, IData data) throws Exception {
		return delete(conns[0], table_name, data);
	}

	/**
	 * delete datas
	 * @param table_name
	 * @param data
	 * @return booleans
	 * @throws Exception
	 */
	public boolean[] deletes(String table_name, IData data) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = delete(conns[i], table_name, data);
		}
		return rss;
	}

	/**
	 * delete data
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @return int[]
	 * @throws Exception
	 */
	private int[] delete(Connection conn, String table_name, IDataset dataset) throws Exception {
		return delete(conn, table_name, dataset, null);
	}

	/**
	 * delete data
	 * @param table_name
	 * @param dataset
	 * @return int[]
	 * @throws Exception
	 */
	public int[] delete(String table_name, IDataset dataset) throws Exception {
		return delete(conns[0], table_name, dataset);
	}

	/**
	 * delete datas
	 * @param table_name
	 * @param dataset
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] deletes(String table_name, IDataset dataset)  throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = delete(conns[i], table_name, dataset);
		}
		return rss;
	}

	/**
	 * delete data
	 * @param conn
	 * @param table_name
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	private boolean delete(Connection conn, String table_name, String[] keys, String[] values) throws Exception {
		return delete(conn, table_name, DaoHelper.getDataByKeys(keys, values), keys);
	}

	/**
	 * delete data
	 * @param table_name
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	public boolean delete(String table_name, String[] keys, String[] values) throws Exception {
		return delete(conns[0], table_name, keys, values);
	}

	/**
	 * delete datas
	 * @param table_name
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	public boolean[] deletes(String table_name, String[] keys, String[] values) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = delete(conns[i], table_name, keys, values);
		}
		return rss;
	}

	/**
	 * delete data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean
	 * @throws Exception
	 */
	private boolean delete(Connection conn, String table_name, IData data, String[] keys) throws Exception {
		Object[] delobjs = DaoHelper.getObjectsByDelete(conn, table_name, data, keys);
		int result = executeUpdate(conn, (String) delobjs[0], (Parameter) delobjs[1]);
		return result == 0 ? false : true;
	}

	/**
	 * delete data
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean
	 * @throws Exception
	 */
	public boolean delete(String table_name, IData data, String[] keys) throws Exception {
		return delete(conns[0], table_name, data, keys);
	}

	/**
	 * delete datas
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean
	 * @throws Exception
	 */
	public boolean[] deletes(String table_name, IData data, String[] keys) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = delete(conns[i], table_name, data, keys);
		}
		return rss;
	}

	/**
	 * delete data
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return int[]
	 * @throws Exception
	 */
	private int[] delete(Connection conn, String table_name, IDataset dataset, String[] keys) throws Exception {
		if (dataset.size() == 0) return null;
		Object[] delobjs = DaoHelper.getObjectsByDelete(conn, table_name, dataset, keys);
		return executeBatch(conn, (String) delobjs[0], (Parameter[]) delobjs[1]);
	}

	/**
	 * delete data
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return int[]
	 * @throws Exception
	 */
	public int[] delete(String table_name, IDataset dataset, String[] keys) throws Exception {
		return delete(conns[0], table_name, dataset, keys);
	}

	/**
	 * delete datas
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return int[][]
	 * @throws Exception
	 */
	public int[][] deletes(String table_name, IDataset dataset, String[] keys)  throws Exception {
		int[][] rss = new int[conns.length][];
		for (int i=0; i<rss.length; i++) {
			rss[i] = delete(conns[i], table_name, dataset, keys);
		}
		return rss;
	}

	/**
	 * save data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	private boolean save(Connection conn, String table_name, IData data) throws Exception {
		return save(conn, table_name, data, null);
	}

	/**
	 * save data
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	public boolean save(String table_name, IData data) throws Exception {
		return save(conns[0], table_name, data);
	}

	/**
	 * save datas
	 * @param table_name
	 * @param data
	 * @return boolean
	 * @throws Exception
	 */
	public boolean[] saves(String table_name, IData data) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = save(conns[i], table_name, data);
		}
		return rss;
	}

	/**
	 * save data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean
	 * @throws Exception
	 */
	private boolean save(Connection conn, String table_name, IData data, String[] keys) throws Exception {
		return save(conn, table_name, data, keys, null);
	}

	/**
	 * save data
	 * @param table_name
	 * @param data
	 * @parma keys
	 * @return boolean
	 * @throws Exception
	 */
	public boolean save(String table_name, IData data, String[] keys) throws Exception {
		return save(conns[0], table_name, data, keys);
	}

	/**
	 * save datas
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return boolean
	 * @throws Exception
	 */
	public boolean[] saves(String table_name, IData data, String[] keys) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = save(conns[i], table_name, data, keys);
		}
		return rss;
	}

	/**
	 * save data
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	private boolean save(Connection conn, String table_name, IData data, String[] keys, String[] values) throws Exception {
		IData alldata = values == null ? queryByPK(conn, table_name, data, keys) : queryByPK(conn, table_name, keys, values);
		if (alldata == null) return false;
		alldata.putAll(data);

		return update(conn, table_name, alldata, keys, values);
	}

	/**
	 * save data
	 * @param table_name
	 * @param data
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	public boolean save(String table_name, IData data, String[] keys, String[] values) throws Exception {
		return save(conns[0], table_name, data, keys, values);
	}

	/**
	 * save datas
	 * @param table_name
	 * @param data
	 * @param keys
	 * @param values
	 * @return boolean
	 * @throws Exception
	 */
	public boolean[] saves(String table_name, IData data, String[] keys, String[] values) throws Exception {
		boolean[] rss = new boolean[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = save(conns[i], table_name, data, keys, values);
		}
		return rss;
	}

	/**
	 * query by pk
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return IData
	 * @throws Exception
	 */
	private IData queryByPK(Connection conn, String table_name, IData data) throws Exception {
		return queryByPK(conn, table_name, data, null);
	}

	/**
	 * query by pk
	 * @param table_name
	 * @param data
	 * @return IData
	 * @throws Exception
	 */
	public IData queryByPK(String table_name, IData data) throws Exception {
		return queryByPK(conns[0], table_name, data);
	}

	/**
	 * query by pks
	 * @param table_name
	 * @param data
	 * @return IData[]
	 * @throws Exception
	 */
	public IData[] queryByPKs(String table_name, IData data) throws Exception {
		IData[] rss = new IData[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryByPK(conns[i], table_name, data);
		}
		return rss;
	}

	/**
	 * query by pk
	 * @param conn
	 * @param table_name
	 * @param keys
	 * @param values
	 * @return IData
	 * @throws Exception
	 */
	private IData queryByPK(Connection conn, String table_name, String[] keys, String[] values) throws Exception {
		return queryByPK(conn, table_name, DaoHelper.getDataByKeys(keys, values), keys);
	}

	/**
	 * query by pk
	 * @param table_name
	 * @param keys
	 * @param values
	 * @return IData
	 * @throws Exception
	 */
	public IData queryByPK(String table_name, String[] keys, String[] values) throws Exception {
		return queryByPK(conns[0], table_name, keys, values);
	}

	/**
	 * query by pks
	 * @param table_name
	 * @param keys
	 * @param values
	 * @return IData[]
	 * @throws Exception
	 */
	public IData[] queryByPKs(String table_name, String[] keys, String[] values) throws Exception {
		IData[] rss = new IData[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryByPK(conns[i], table_name, keys, values);
		}
		return rss;
	}

	/**
	 * query by pk
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return IData
	 * @throws Exception
	 */
	private IData queryByPK(Connection conn, String table_name, IData data, String[] keys) throws Exception {
		Object[] qryobjs = DaoHelper.getObjectsByQuery(conn, table_name, data, keys);
		IDataset dataset = queryList(conn, (String) qryobjs[0], (Parameter) qryobjs[1]);
		return dataset.size() == 0 ? null : (IData) dataset.get(0);
	}

	/**
	 * query by pk
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return IData
	 * @throws Exception
	 */
	public IData queryByPK(String table_name, IData data, String[] keys) throws Exception {
		return queryByPK(conns[0], table_name, data, keys);
	}

	/**
	 * query by pks
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return IData[]
	 * @throws Exception
	 */
	public IData[] queryByPKs(String table_name, IData data, String[] keys) throws Exception {
		IData[] rss = new IData[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryByPK(conns[i], table_name, data, keys);
		}
		return rss;
	}

	/**
	 * query list
	 * @param conn
	 * @param sql
	 * @return IDataset
	 * @throws Exception
	 */
	private IDataset queryList(Connection conn, String sql) throws Exception {
		ResultSet rs = executeQuery(conn, sql);
		IDataset dataset = new DatasetResult(rs);
		rs.getStatement().close();
		return dataset;
	}

	/**
	 * query list
	 * @param sql
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql) throws Exception {
		return queryList(conns[0], sql);
	}

	/**
	 * query lists
	 * @param sql
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql) throws Exception {
		IDataset[] rss = new IDataset[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryList(conns[i], sql);
		}
		return rss;
	}

	/**
	 * query list
	 * @param conn
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	private IDataset queryList(Connection conn, String sql, Parameter param) throws Exception {
		if (param == null || param.size() == 0) return queryList(conn, sql);
		ResultSet rs = executeQuery(conn, sql, param);
		IDataset dataset = new DatasetResult(rs);
		rs.getStatement().close();
		return dataset;
	}

	/**
	 * query list
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql, Parameter param) throws Exception {
		return queryList(conns[0], sql, param);
	}

	/**
	 * query lists
	 * @param sql
	 * @param param
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql, Parameter param) throws Exception {
		IDataset[] rss = new IDataset[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryList(conns[i], sql, param);
		}
		return rss;
	}

	/**
	 * query list
	 * @param conn
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	private IDataset queryList(Connection conn, String sql, IData param) throws Exception {
		if (param == null) return queryList(conn, sql);
		ResultSet rs = executeQuery(conn, sql, param);
		IDataset dataset = new DatasetResult(rs);
		rs.getStatement().close();
		return dataset;
	}

	/**
	 * query list
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql, IData param) throws Exception {
		return queryList(conns[0], sql, param);
	}

	/**
	 * query lists
	 * @param sql
	 * @param param
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql, IData param) throws Exception {
		IDataset[] rss = new IDataset[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryList(conns[i], sql, param);
		}
		return rss;
	}

	/**
	 * query list
	 * @param conn
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset
	 * @throws Exception
	 */
	private IDataset queryList(Connection conn, String sql, Parameter param, Pagination pagination) throws Exception {
		//ejb方式提供接口  取记录总条数
		//add by zhujm 2007-3-26
		if (pagination != null && pagination.isOnlyCount()) {
			DatasetList list = new DatasetList();
			IData tmp = new DataMap();
			tmp.put("X_SELCOUNT", String.valueOf(getCount(conn, sql, param)));
			list.add(tmp);
			return list;
		}

		boolean isrange = pagination != null && pagination.isRange();
		int count = isrange ? (pagination.isNeedCount() ? getCount(conn, sql, param) : pagination.getCount()) : 0;
		boolean isbatch = pagination != null && pagination.isBatch();

		if (isbatch) {
			return getTotalDataset(conn, sql, param, count, pagination.getSize());
		} else {
			if (isrange) {
				if (param == null) param = new Parameter();
				sql = DaoHelper.getPagingSql(sql, param, pagination.getStart(), pagination.getStart() + pagination.getSize());
			}

			DatasetResult dataset = (DatasetResult) queryList(conn, sql, param);
			dataset.setCount(count);
			return dataset;
		}
	}

	/**
	 * query list
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql, Parameter param, Pagination pagination) throws Exception {
		return queryList(conns[0], sql, param, pagination);
	}

	/**
	 * query lists
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql, Parameter param, Pagination pagination) throws Exception {
		IDataset[] rss = new IDataset[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryList(conns[i], sql, param, pagination);
		}
		return rss;
	}

	/**
	 * query list
	 * @param conn
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset
	 * @throws Exception
	 */
	private IDataset queryList(Connection conn, String sql, IData param, Pagination pagination) throws Exception {
		//ejb方式提供接口  取记录总条数
		//add by zhujm 2007-3-26
		if (pagination != null && pagination.isOnlyCount()) {
			DatasetList list = new DatasetList();
			IData tmp = new DataMap();
			tmp.put("X_SELCOUNT", String.valueOf(getCount(conn, sql, param)));
			list.add(tmp);
			return list;
		}

		boolean isrange = pagination != null && pagination.isRange();
		int count = isrange ? (pagination.isNeedCount() ? getCount(conn, sql, param) : pagination.getCount()) : 0;
		boolean isbatch = pagination != null && pagination.isBatch();

		if (isbatch) {
			return getTotalDataset(conn, sql, param, count, pagination.getSize());
		} else {
			if (isrange) {
				if (param == null) param = new DataMap();
				sql = DaoHelper.getPagingSql(sql, param, pagination.getStart(), pagination.getStart() + pagination.getSize());
			}

			DatasetResult dataset = (DatasetResult) queryList(conn, sql, param);
			dataset.setCount(count);
			return dataset;
		}
	}

	/**
	 * query list
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql, IData param, Pagination pagination) throws Exception {
		return queryList(conns[0], sql, param, pagination);
	}

	/**
	 * query lists
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql, IData param, Pagination pagination) throws Exception {
		IDataset[] rss = new IDataset[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = queryList(conns[i], sql, param, pagination);
		}
		return rss;
	}

	/**
	 * call func
	 * @param conn
	 * @param name
	 * @param paramNames
	 * @param params
	 * @param returnType
	 * @return Object
	 * @throws Exception
	 */
	private Object callFunc(Connection conn, String name, String[] paramNames, IData params, int returnType) throws Exception {
		return DaoHelper.callFunc(conn, name, paramNames, params, returnType);
	}

	/**
	 * call func
	 * @param conn
	 * @param name
	 * @param paramNames
	 * @param params
	 * @param returnType
	 * @return Object
	 * @throws Exception
	 */
	public Object callFunc(String name, String[] paramNames, IData params, int returnType) throws Exception {
		return callFunc(conns[0], name, paramNames, params, returnType);
	}

	/**
	 * call funcs
	 * @param conn
	 * @param name
	 * @param paramNames
	 * @param params
	 * @param returnType
	 * @return Object
	 * @throws Exception
	 */
	public Object[] callFuncs(String name, String[] paramNames, IData params, int returnType) throws Exception {
		Object[] rss = new Object[conns.length];
		for (int i=0; i<rss.length; i++) {
			rss[i] = callFunc(conns[i], name, paramNames, params, returnType);
		}
		return rss;
	}

	/**
	 * call proc
	 * @param conn
	 * @param name
	 * @param paramNames
	 * @param params
	 * @throws Exception
	 */
	private void callProc(Connection conn, String name, String[] paramNames, IData params) throws Exception {
		DaoHelper.callProc(conn, name, paramNames, params);
	}

	/**
	 * call proc
	 * @param name
	 * @param paramNames
	 * @param params
	 * @throws Exception
	 */
	public void callProc(String name, String[] paramNames, IData params) throws Exception {
		callProc(conns[0], name, paramNames, params);
	}

	/**
	 * call procs
	 * @param name
	 * @param paramNames
	 * @param params
	 * @throws Exception
	 */
	public void callProcs(String name, String[] paramNames, IData params) throws Exception {
		for (int i=0; i<conns.length; i++) {
			callProc(conns[i], name, paramNames, params);
		}
	}

	/** private begin */
	/**
	 * set dataset serializable
	 * @param dataset
	 * @throws Exception
	 */
	private void setDatasetSerializable(IDataset dataset, int pageCount, int pageSize) throws Exception {
		dataset.setSerializable(true);
		dataset.setBatchSerializable(true);
		dataset.setSerializablePath(common.getProperty("project/uploadpath") + "/" + FileMan.getUploadPath(FileMan.UPLOAD_TYPE_TEMP));
		dataset.setSerializableId(BaseFactory.getUniqeName());
		dataset.setBatchPageCount(pageCount);
		dataset.setBatchPageSize(pageSize);

		File file = new File(dataset.getSerializablePath() + "/" + dataset.getSerializableId());
		if (!file.exists()) file.mkdir();
	}

	/**
	 * get total dataset
	 * @param conn
	 * @param sql
	 * @param param
	 * @param rowCount
	 * @param maxPageSize
	 * @return IDataset
	 * @throws Exception
	 */
	private IDataset getTotalDataset(Connection conn, String sql, Parameter param, int rowCount, int maxPageSize) throws Exception {
		IDataset dataset = new DatasetList();

		int pageCount = (int) Math.ceil((double) rowCount / (double) maxPageSize);

		if (pageCount > 1) {
			setDatasetSerializable(dataset, pageCount, maxPageSize);
		}

		for (int i=0; i<pageCount; i++) {
			Parameter subparam = new Parameter();
			subparam.addAll(param);
			String subsql = DaoHelper.getPagingSql(sql, subparam, i * maxPageSize, i * maxPageSize + maxPageSize);

			IDataset subset = queryList(conn, subsql, subparam);
			if (dataset.isSerializable()) {
				FileMan.writeObject(dataset.getSerializablePath() + "/" + dataset.getSerializableId() + "/" + i, subset);
			} else {
				dataset.addAll(queryList(conn, subsql, subparam));
			}
		}

		return dataset;
	}

	/**
	 * get total dataset
	 * @param conn
	 * @param sql
	 * @param param
	 * @param rowCount
	 * @param maxPageSize
	 * @return IDataset
	 * @throws Exception
	 */
	private IDataset getTotalDataset(Connection conn, String sql, IData param, int rowCount, int maxPageSize) throws Exception {
		IDataset dataset = new DatasetList();

		int pageCount = (int) Math.ceil((double) rowCount / (double) maxPageSize);

		if (pageCount > 1) {
			setDatasetSerializable(dataset, pageCount, maxPageSize);
		}

		for (int i=0; i<pageCount; i++) {
			IData subparam = new DataMap();
			subparam.putAll(param);
			String subsql = DaoHelper.getPagingSql(sql, subparam, i * maxPageSize, i * maxPageSize + maxPageSize);

			IDataset subset = queryList(conn, subsql, subparam);
			if (dataset.isSerializable()) {
				FileMan.writeObject(dataset.getSerializablePath() + "/" + dataset.getSerializableId() + "/" + i, subset);
			} else {
				dataset.addAll(queryList(conn, subsql, subparam));
			}
		}

		return dataset;
	}
	/** private end */

}